从原有32位Windows系统的Oracle10g迁移到64位Linux的Oracle11g，没选择整库迁移，对于SYSTEM及其它系统用户不做迁移，所以要对SYSTEM查看下相关的内容，完成后恢复到新的数据库，这里先看看有没有在这个用户的自定义对象，文章后面有具体操作步骤。
select * from v$version; -- 查询触发器 select * from all_triggers where owner in ('PBXY'); -- 查询序列 select SEQUENCE_OWNER,SEQUENCE_NAME from dba_sequences where sequence_owner in ('PBXY'); -- 查询数据库连接 select * from dba_db_links; select * from dba_objects where object_type='DATABASE LINK'; select * from user_objects t where t.object_type='DATABASE LINK'; -- 查询同义词 SELECT * FROM SYS.ALL_SYNONYMS;
从10g导出
在源机器上准备备份
-- 查看是否已经有DMP目录 select * from dba_directories where directory_name='DMP_DIR'; -- 如果没有请建立 create or replace directory dmp_dir as 'D:/databak'; -- 为用建立权限 grant read,write on directory dmp_dir to system; -- 查看权限 select * from dba_tab_privs where GRANTEE='SYSTEM' and table_name='DMP_DIR';
导出数据
set today=%date:~0,4%%date:~5,2%%date:~-2% expdp system/oradbpass@127.0.0.1/orclpbxy directory=dmp_dir dumpfile=yth_expdp-20_219-%today%.dmp logfile=yth_expdp-20_219-%today%.log full=y
从11g上使用dblink导出
10g的IP为192.168.20.219
CREATE PUBLIC DATABASE LINK yth_20_219 CONNECT TO system IDENTIFIED BY orapwd USING '192.168.20.219:1521/orclpbxy'; -- 也可以如下方式，如果需要连接集群等特性时 CREATE PUBLIC DATABASE LINK yth_20_219 CONNECT TO system IDENTIFIED BY orapwd USING '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.20.219)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orclpbxy) ) )';
11g本机IP为192.168.20.73
# 使用数据库用户 expdp system/orapwd@192.168.20.73/orclpbxy directory=dmp_dir dumpfile=yth_expdp-20_219-today.dmp logfile=yth_expdp-20_219-today.log full=y network_link=yth_20_219
完成后删除dblink
DROP PUBLIC DATABASE LINK yth_20_219;
好像不行。。。
从11g上导入数据库
-- 如果没有请建立 create or replace directory dmp_dir as '/home/oracle/orabak'; -- 为用建立权限 grant read,write on directory dmp_dir to system;
准备表空间
CREATE TABLESPACE "myddc" DATAFILE '/u01/app/oradata/orclpbxy/MYDDC.DBF' SIZE 41943040 AUTOEXTEND ON NEXT 102400K MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO; CREATE TABLESPACE "TSP_ATT" DATAFILE '/u01/app/oradata/orclpbxy/AP_ATT.DBF' SIZE 104857600 AUTOEXTEND ON NEXT 102400K MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO; CREATE TABLESPACE "TSP_COMM" DATAFILE '/u01/app/oradata/orclpbxy/APCOMM.DBF' SIZE 104857600 AUTOEXTEND ON NEXT 102400K MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO; CREATE TABLESPACE "TSP_MYDDC" DATAFILE '/u01/app/oradata/orclpbxy/APMYDDC.DBF' SIZE 104857600 AUTOEXTEND ON NEXT 102400K MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO; CREATE TABLESPACE "TSP_KAOQIN" DATAFILE '/u01/app/oradata/orclpbxy/AP_KAOQIN.DBF' SIZE 104857600 AUTOEXTEND ON NEXT 102400K MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO; CREATE TABLESPACE "TSP_PBXY" DATAFILE '/u01/app/oradata/orclpbxy/APPBXY.DBF' SIZE 104857600 AUTOEXTEND ON NEXT 102400K MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO, '/u01/app/oradata/orclpbxy/APPBXY01.DBF' SIZE 10737418240 AUTOEXTEND ON NEXT 102400K MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
清除SCHEMA，如果已经导入过了，再次导入先把就的清理了。其实根据观察导出只有前4个用户有数据。
drop user PBXY cascade; drop user MYDDC cascade; drop user KAOQIN cascade; drop user COMM cascade; drop user EE cascade; drop user EXAM cascade; drop user ORDADM cascade; drop user INPADM cascade; drop user PBXY_NEW cascade; drop user SURGERY cascade; drop user OUTPADM cascade; drop user INPBILL cascade; drop user RLZY cascade; drop user LAB cascade; drop user rlzy cascade; drop user OUTPBILL cascade; drop user OUTPDOCT cascade; drop user outpadm cascade; drop user MEDREC cascade; drop user YWGL cascade;
执行导入
命令impdp的详细说明：Data Pump Import (oracle.com)
# 这个语句是整库导入，就不用了，看下面的 impdp system/oradbpass@orclpbxy DIRECTORY=dmp_dir DUMPFILE=exptable.dmp table_exists_action=REPLACE # 使用数据库用户名密码认证并导入 impdp system/oracle@orclpbxy DIRECTORY=dmp_dir DUMPFILE=YTH_EXPDP-20_219-20220312.DMP table_exists_action=REPLACE SCHEMAS=PBXY,KAOQIN,MYDDC,COMM,EE,EXAM,ORDADM,INPADM,PBXY_NEW,SURGERY,OUTPADM,INPBILL,RLZY,LAB,rlzy,OUTPBILL,OUTPDOCT,outpadm,MEDREC,YWGL LOGFILE=yth-impdp-20220312.log PARALLEL=10 # 使用操作系统认证方式的SYS用户 impdp \'/ as sysdba\' DIRECTORY=dmp_dir DUMPFILE=YTH_EXPDP-20_219-20220319.DMP table_exists_action=REPLACE SCHEMAS=PBXY,KAOQIN,MYDDC,COMM,EE,EXAM,ORDADM,INPADM,PBXY_NEW,SURGERY,OUTPADM,INPBILL,RLZY,LAB,rlzy,OUTPBILL,OUTPDOCT,outpadm,MEDREC,YWGL LOGFILE=yth-impdp-20220319.log PARALLEL=10
export NLS_LANG="SIMPLIFIED CHINESE_CHINA.AL32UTF8"
恢复SYSTEM用户相关内容
因为老的系统使用是SYSTEM用户，太尴尬了，还得把SYSTEM的数据库连接、同义词、触发器、序列移过来。
经观察SYSTEM中并没有用户自建序列，也没有自建触发器。所以只恢复数据库连接和同义词。
恢复数据库连接
如果dblink是system或者public的，那么导入时没有选择这两个schema，不是全库导入，dblink不包含在里面。
先在tnsnames.ora中加入服务
CAYY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.20.111)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) )
建立数据库连接
CREATE PUBLIC DATABASE LINK "HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM" CONNECT TO "PBXY" IDENTIFIED BY VALUES '05B1E9A21D404963E150EDAE44199FB790' using 'CAYY';
恢复同义词
为SYSTEM用户恢复同义词
查询同义词
SELECT * FROM SYS.ALL_SYNONYMS where table_owner not in ( 'SYS', 'ORDSYS', 'MDSYS', 'OLAPSYS', 'WMSYS', 'CTXSYS', 'XDB', 'SYSMAN', 'DMSYS', 'EXFSYS', 'SYSTEM', 'RLZY' ) ORDER BY db_link DESC; SELECT 'CREATE OR REPLACE SYNONYM "'||OWNER||'"."'||SYNONYM_NAME||'" FOR "'||TABLE_OWNER||'"."'||TABLE_NAME||'"'|| CASE WHEN DB_LINK IS NOT NULL THEN '@"'||DB_LINK||'"' ELSE '' END ||';' FROM SYS.ALL_SYNONYMS where table_owner not in ( 'SYS', 'ORDSYS', 'MDSYS', 'OLAPSYS', 'WMSYS', 'CTXSYS', 'XDB', 'SYSMAN', 'DMSYS', 'EXFSYS', 'SYSTEM', 'RLZY' );
建立同义词
CREATE OR REPLACE SYNONYM "PUBLIC"."ADMINISTRATION_DICT_TF" FOR "PBXY"."ADMINISTRATION_DICT_TF"; CREATE OR REPLACE SYNONYM "PUBLIC"."DRUG_ANTI_DICT" FOR "PBXY"."DRUG_ANTI_DICT"; CREATE OR REPLACE SYNONYM "PUBLIC"."F_GET_NUM" FOR "PBXY"."F_GET_NUM"; CREATE OR REPLACE SYNONYM "PUBLIC"."MEMO_DICT" FOR "PBXY"."MEMO_DICT"; CREATE OR REPLACE SYNONYM "PUBLIC"."PATS_IN_TRANSFUSION" FOR "PBXY"."PATS_IN_TRANSFUSION"; CREATE OR REPLACE SYNONYM "PUBLIC"."PRINTMEMO_DICT" FOR "PBXY"."PRINTMEMO_DICT"; CREATE OR REPLACE SYNONYM "PUBLIC"."P_SPLIT_ORDERS" FOR "PBXY"."P_SPLIT_ORDERS"; CREATE OR REPLACE SYNONYM "PUBLIC"."SEAT_REC" FOR "PBXY"."SEAT_REC"; CREATE OR REPLACE SYNONYM "PUBLIC"."SEQ_SHIFT_NO" FOR "PBXY"."SEQ_SHIFT_NO"; CREATE OR REPLACE SYNONYM "PUBLIC"."SHIFT_LOG" FOR "PBXY"."SHIFT_LOG"; CREATE OR REPLACE SYNONYM "PUBLIC"."STAFF_VS_GROUP" FOR "PBXY"."STAFF_VS_GROUP"; CREATE OR REPLACE SYNONYM "PUBLIC"."TRANSFUSION_EXECUTE" FOR "PBXY"."TRANSFUSION_EXECUTE"; CREATE OR REPLACE SYNONYM "PUBLIC"."TRANSFUSION_LOG" FOR "PBXY"."TRANSFUSION_LOG"; CREATE OR REPLACE SYNONYM "PUBLIC"."TRANSFUSION_ORDERS" FOR "PBXY"."TRANSFUSION_ORDERS"; CREATE OR REPLACE SYNONYM "PUBLIC"."TRANSFUSION_PRINTER" FOR "PBXY"."TRANSFUSION_PRINTER"; CREATE OR REPLACE SYNONYM "PUBLIC"."TRANSFUSION_SCHEDULE" FOR "PBXY"."TRANSFUSION_SCHEDULE"; CREATE OR REPLACE SYNONYM "SYSTEM"."ADT_LOG" FOR "INPADM"."ADT_LOG"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."AREA_DICT" FOR "COMM"."AREA_DICT"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."BED_REC" FOR "INPADM"."BED_REC"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."BILL_ITEM_CLASS_DICT" FOR "COMM"."BILL_ITEM_CLASS_DICT"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."BINGLI_KESHEOPEN" FOR "COMM"."BINGLI_KESHEOPEN"; CREATE OR REPLACE SYNONYM "SYSTEM"."BINGLI_OPEN" FOR "COMM"."BINGLI_OPEN"; CREATE OR REPLACE SYNONYM "SYSTEM"."CHARGE_PRICE_SCHEDULE" FOR "COMM"."CHARGE_PRICE_SCHEDULE"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."CHARGE_SPECIAL_EXCEPT_DICT" FOR "COMM"."CHARGE_SPECIAL_EXCEPT_DICT"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."CHARGE_SPECIAL_ITEM_DICT" FOR "COMM"."CHARGE_SPECIAL_ITEM_DICT"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."CLINICAL_DEPT_CONFIG" FOR "COMM"."CLINICAL_DEPT_CONFIG"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."CLINICLAYER" FOR "COMM"."CLINICLAYER"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."CLINIC_FOR_REGIST" FOR "OUTPADM"."CLINIC_FOR_REGIST"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."CLINIC_INDEX" FOR "OUTPADM"."CLINIC_INDEX"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."CLINIC_MASTER" FOR "OUTPADM"."CLINIC_MASTER"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."CLINIC_RETURNED_ACCT" FOR "OUTPADM"."CLINIC_RETURNED_ACCT"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."CLINIC_TYPE_CHARGE_DICT" FOR "COMM"."CLINIC_TYPE_CHARGE_DICT"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."CLINIC_TYPE_SETTING" FOR "COMM"."CLINIC_TYPE_SETTING"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."COUNTRY_DICT" FOR "COMM"."COUNTRY_DICT"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."CURRENT_PRICE_LIST" FOR "COMM"."CURRENT_PRICE_LIST"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."DENTITY_DICT" FOR "COMM"."DENTITY_DICT"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."DEPT_CLINIC_ATTR_DICT" FOR "COMM"."DEPT_CLINIC_ATTR_DICT"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."DEPT_DICT" FOR "COMM"."DEPT_DICT"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."DEPT_DICT_JIANINFO" FOR "COMM"."DEPT_DICT_JIANINFO"; CREATE OR REPLACE SYNONYM "SYSTEM"."DEPT_IS_ATTR_DICT" FOR "COMM"."DEPT_IS_ATTR_DICT"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."DEPT_OI_ATTR_DICT" FOR "COMM"."DEPT_OI_ATTR_DICT"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."DEPT_VS_WARD" FOR "COMM"."DEPT_VS_WARD"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."DIAGNOSIS" FOR "MEDREC"."DIAGNOSIS"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."DIAGNOSIS_TYPE_DICT" FOR "COMM"."DIAGNOSIS_TYPE_DICT"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."DRUG_DICT" FOR "COMM"."DRUG_DICT"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."DRUG_FORM_DICT" FOR "COMM"."DRUG_FORM_DICT"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."DRUG_PRICE_LIST" FOR "COMM"."DRUG_PRICE_LIST"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."DRUG_TOXI_PROPERTY_DICT" FOR "COMM"."DRUG_TOXI_PROPERTY_DICT"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."EV_DOCTOR_INTR" FOR "COMM"."EV_DOCTOR_INTR"; CREATE OR REPLACE SYNONYM "SYSTEM"."EXAM_MASTER" FOR "EXAM"."EXAM_MASTER"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."EXPERT_DICT_JIANINFO" FOR "COMM"."EXPERT_DICT_JIANINFO"; CREATE OR REPLACE SYNONYM "SYSTEM"."HOSPITAL_CONFIG" FOR "COMM"."HOSPITAL_CONFIG"; CREATE OR REPLACE SYNONYM "SYSTEM"."IDENTITY_DICT" FOR "COMM"."IDENTITY_DICT"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."INP_BILL_DETAIL" FOR "INPBILL"."INP_BILL_DETAIL"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."ITEM" FOR "LAB"."ITEM"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."ITEM_TYPE_DICT" FOR "LAB"."ITEM_TYPE_DICT"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."JOB_CLASS_DICT" FOR "COMM"."JOB_CLASS_DICT"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."LAB_ITEM_DICT" FOR "COMM"."LAB_ITEM_DICT"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."LAB_ORDER_VS_REPORT" FOR "COMM"."LAB_ORDER_VS_REPORT"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."LAB_REPORT_ITEM_DICT" FOR "COMM"."LAB_REPORT_ITEM_DICT"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."LAB_RESULT" FOR "LAB"."LAB_RESULT"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."LAB_SHEET_ITEMS" FOR "COMM"."LAB_SHEET_ITEMS"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."LAB_TEST_ITEMS" FOR "LAB"."LAB_TEST_ITEMS"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."LAB_TEST_MASTER" FOR "LAB"."LAB_TEST_MASTER"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."MEDICAL_CARD_MEMO" FOR "MEDREC"."MEDICAL_CARD_MEMO"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."MR_FILE_INDEX" FOR "MEDREC"."MR_FILE_INDEX"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."NATION_DICT" FOR "COMM"."NATION_DICT"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."NURSING_CLASS_DICT" FOR "COMM"."NURSING_CLASS_DICT"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."OPERATION" FOR "MEDREC"."OPERATION"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."OPERATION_MASTER" FOR "SURGERY"."OPERATION_MASTER"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."OPERATION_SCHEDULE" FOR "SURGERY"."OPERATION_SCHEDULE"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."ORDERS" FOR "ORDADM"."ORDERS"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."ORDERS_COSTS" FOR "ORDADM"."ORDERS_COSTS"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."ORDER_CLASS_DICT" FOR "ORDADM"."ORDER_CLASS_DICT"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."OUTP_BILL_ITEMS" FOR "OUTPBILL"."OUTP_BILL_ITEMS"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."OUTP_CONTEXT" FOR "COMM"."OUTP_CONTEXT"; CREATE OR REPLACE SYNONYM "SYSTEM"."OUTP_MR" FOR "OUTPDOCT"."OUTP_MR"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."OUTP_ORDER_DESC" FOR "OUTPBILL"."OUTP_ORDER_DESC"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."PATIENT_PSW" FOR "COMM"."PATIENT_PSW"; CREATE OR REPLACE SYNONYM "SYSTEM"."PATIENT_STATUS_CHG_DICT" FOR "COMM"."PATIENT_STATUS_CHG_DICT"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."PATIENT_STATUS_DICT" FOR "COMM"."PATIENT_STATUS_DICT"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."PATS_IN_HOSPITAL" FOR "INPADM"."PATS_IN_HOSPITAL"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."PATS_IN_TRANSFERRING" FOR "INPADM"."PATS_IN_TRANSFERRING"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."PAT_MASTER_INDEX" FOR "MEDREC"."PAT_MASTER_INDEX"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."PAT_PRINTCONTROL" FOR "COMM"."PAT_PRINTCONTROL"; CREATE OR REPLACE SYNONYM "SYSTEM"."PAT_VISIT" FOR "MEDREC"."PAT_VISIT"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."PRICE_ITEM_NAME_DICT" FOR "COMM"."PRICE_ITEM_NAME_DICT"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."PRICE_LIST" FOR "COMM"."PRICE_LIST"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."P_EV_DOCTOR_QANSWERTOTAL" FOR "COMM"."P_EV_DOCTOR_QANSWERTOTAL"; CREATE OR REPLACE SYNONYM "SYSTEM"."P_EV_DOCTOR_QUESTION" FOR "COMM"."P_EV_DOCTOR_QUESTION"; CREATE OR REPLACE SYNONYM "SYSTEM"."P_EV_DOCTOR_QUESTIONANSWER" FOR "COMM"."P_EV_DOCTOR_QUESTIONANSWER"; CREATE OR REPLACE SYNONYM "SYSTEM"."P_EV_DOCTOR_TEMPLATE" FOR "COMM"."P_EV_DOCTOR_TEMPLATE"; CREATE OR REPLACE SYNONYM "SYSTEM"."P_EV_OHTERCLINICNAME" FOR "COMM"."P_EV_OHTERCLINICNAME"; CREATE OR REPLACE SYNONYM "SYSTEM"."RESULTS" FOR "LAB"."RESULTS"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."SEX_DICT" FOR "COMM"."SEX_DICT"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."STAFF_DICT" FOR "COMM"."STAFF_DICT"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."STAFF_GROUP_CLASS_DICT" FOR "COMM"."STAFF_GROUP_CLASS_DICT"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."STAFF_GROUP_DICT" FOR "COMM"."STAFF_GROUP_DICT"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."STAFF_VS_GROUP" FOR "COMM"."STAFF_VS_GROUP"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."TOUCHSYSTEM_SET" FOR "COMM"."TOUCHSYSTEM_SET"; CREATE OR REPLACE SYNONYM "SYSTEM"."TRANSFER" FOR "MEDREC"."TRANSFER"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."USERS" FOR "COMM"."USERS"@"HIS.REGRESS.RDBMS.DEV.US.ORACLE.COM"; CREATE OR REPLACE SYNONYM "SYSTEM"."YLCS_CONTENT" FOR "COMM"."YLCS_CONTENT"; CREATE OR REPLACE SYNONYM "SYSTEM"."YLCS_MC" FOR "COMM"."YLCS_MC"; CREATE OR REPLACE SYNONYM "SYSTEM"."ZTHL_HLJLD_SY" FOR "PBXY"."ZTHL_HLJLD_SY";
恢复表
impdp \'/ as sysdba\' DIRECTORY=dmp_dir DUMPFILE=YTH_EXPDP-20_219-20220319.DMP tables=system.user_NUSUR LOGFILE=yth-impdp-20220320.log
数据库基础设置
默认数据库允许的最大的process数量是150个，包括系统的process，需要修改process数量，session数量不用修改，Oracle 11g会自己计算。
-- 修改参数 alter system set processes=500 scope=spfile; commit; -- 重启数据库才能生效 shudown immediate startup -- 查看参数 show parameter process;
默认的用户是180天过期，实际使用中有可能无法根据周期去变更
-- 修改为不限制 alter profile default limit PASSWORD_LIFE_TIME UNLIMITED; -- 查看修改结果 select * from dba_profiles s where s.profile='DEFAULT' and resource_name='PASSWORD_LIFE_TIME';